<?php

namespace app\order\logic;

use app\data\util\HttpUtil;
use think\facade\Db;
use think\facade\Cache;
use util\Excel;
use think\exception\ValidateException;
use think\facade\Log;
use util\Token;
use util\Upload;
use app\api\logic\ApiOrderLogic;

class OrderFeedbackLogic
{
    public function __construct()
    {
    }

    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 反馈问题列表
     * @author zhangheng
     */
    public function qustionList($params = [])
    {
        // $key  = 'order_qustion_three';
        // $data =  Cache::store('redis')->get($key);
        // if ($data) {
            // return json(DefaultResponse(200, true, "查询成功", $data));
        // } else {
            $data      = Db::name('order_qustion_level')->field('type_id as value,parent_id,type_name as label')->where('status',0)->select()->toArray();
            $info_tree = listToTree($data, 'value', 'parent_id', 'children');
            //文件缓存一份;
            // Cache::store('redis')->set($key,$info_tree,86400*7);
            return json(DefaultResponse(200, true, "查询成功", $info_tree ));
        // }
    }
    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc find
     * @author zhangheng
     */
    public function find($order_id)
    {
        if(empty($order_id)){
            return json(DefaultResponse(201, false, '订单号错误!', null));
        }
        
        $order_info = Db::name('order_info')
            ->field('
                info_id,
                name,
                sku
            ')
            ->where('order_id',$order_id)
            ->select()
            ->toarray();

        if (empty($order_info)) {
            return json(DefaultResponse(201, false, '数据不存在!', null));
        }
            
        foreach ($order_info as $k=>$v) {
            $order_qustioninfo = Db::name('order_qustioninfo')
                ->field('
                    qustion_ids,
                    note
                ')
                ->where('orderinfo_id',$v['info_id'])
                ->select()
                ->toarray();
            $order_info[$k]['qustion_id'] = [];
            if(!empty($order_qustioninfo)){
                $order_info[$k]['note'] = $order_qustioninfo[0]['note'];
                //反馈问题是数组
                foreach ($order_qustioninfo as $kq => &$vq) {
                    if (is_string($vq['qustion_ids'])) {
                        $v['qustion_ids'] = json_decode($vq['qustion_ids'], true);
                        $order_info[$k]['qustion_id'][] = $v['qustion_ids']; 
                    } 
                }
            }
            
        }
        return json(DefaultResponse(200, true, '获取成功', $order_info));
    }

    /**
    * @desc    订单反馈编辑
    * @date    2024/7/16
    * @author  zhangheng
    */
    public function edit($data = [])
    {
        if (!$data) {
            return json(DefaultResponse(201, false, '参数不能为空!', null));
        }
        $result = false;

        $ApiOrderLogic = new ApiOrderLogic();
        try {
            $order_id = 0;
            $order_qustioninfos = [];
            foreach ($data['list'] as $k=>$v) {
                $order_info = Db::name('order_info')
                    ->field('a.*,b.createtime')
                    ->alias('a')
                    ->join('qu_order b','b.order_id = a.order_id', 'left')
                    ->where('a.info_id',$v['info_id'])
                    ->find();
                $order_qustioninfo = [];
                $order_id = $order_info['order_id'];
                $order_qustioninfo['order_id'] = $order_info['order_id'];
                $order_qustioninfo['orderinfo_id'] = $order_info['info_id'];
                $order_qustioninfo['shop_id'] = $order_info['shop_id'];
                $order_qustioninfo['product_id'] = $order_info['product_id'];
                $order_qustioninfo['product_name'] = $order_info['name'];
                $order_qustioninfo['variant_id'] = $order_info['variant_id'];
                $order_qustioninfo['sku'] = $order_info['sku'];


                //状态更新成已反馈
                Db::name('order_info')
                    ->where('info_id',$order_qustioninfo['orderinfo_id'])
                    ->update(
                        [
                            'feedback' => 1
                        ]
                    );
                

                $dashPos = strpos($order_info['sku'], '-');  
                if ($dashPos !== false) {  
                    $order_qustioninfo['spu'] = substr($order_info['sku'], 0, $dashPos);  
                }
                
                $order_qustioninfo['note'] = "";
                if(!empty($v['note'])){
                    // $order_qustioninfo['note'] = filterEmoji($v['note']);
                    $order_qustioninfo['note'] = $ApiOrderLogic->removeNonAscii($v['note']);
                }

                $order_qustioninfo['created_at'] = time();
                $order_qustioninfo['createtime'] = $order_info['createtime'];
                if(!empty($v['qustion_id'])){
                    foreach ($v['qustion_id'] as $kq=>$vq) {
                        $order_qustioninfo['qustion_ids'] = json_encode($vq);
                        if(count($vq) == 3){
                            $order_qustioninfo['qustion_id_parent'] = $vq[1];
                            $order_qustioninfo['qustion_id'] = $vq[2];
                            $order_qustioninfos[] = $order_qustioninfo;
                        }else{
                            $order_qustioninfo['qustion_id_parent'] = $vq[0];
                            $order_qustioninfo['qustion_id'] = $vq[1];
                            $order_qustioninfos[] = $order_qustioninfo;
                        }
                    }
                }
            }
            if(!empty($order_id) && !empty($order_qustioninfos)){
                Db::name('order_qustioninfo')->where('order_id',$order_id)->delete();
                Db::name('order_qustioninfo')->insertAll($order_qustioninfos);
            }
            
            $result = true;
            
            if ($result) {
                return json(DefaultResponse(200, false, '更新成功!', null));
            }
            return json(DefaultResponse(201, false, '更新失败!', null));
        } catch (ValidateException $e) {
            return json(DefaultResponse(201, false, $e->getError(), null));
        } catch (\Exception $e) {
            return json(DefaultResponse(201, false, '更新失败!', null));
        }
        return json(DefaultResponse(200, false, '更新成功!', null));
    }

    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 产品反馈列表
     * @author zhangheng
     */
    public function ProductFeedbackList($params = [])
    {
        $limit = $params['limit'] ?? 10;
        $createtime = $params['createtime'] ?? "";
        $skus = $params['skus'] ?? "";

        $where = [];

        if (!empty($createtime)) {
            $where[] = ['createtime', '>=', strtotime($createtime['0'])];
            $where[] = ['createtime', '<=', strtotime($createtime['1']) + 3600*24-1];

            $where2[] = ['createtime', '>=', $createtime['0']];
            $where2[] = ['createtime', '<=', date('Y-m-d H:i:s', strtotime($createtime['1']) + 3600*24-1)];
        }else{
            return json(DefaultResponse(201, true, '请填写下单时间'));
        }
        if (!empty($skus)) {
            $where[] = ['new_sku', 'LIKE', '%' . trim($skus) . '%'];
        }
        $order['skuquantity'] = 'desc';
        $where[]  = ['financial_status', 'in', ["partially_paid", "paid", "partially_refunded", "refunded"]];
        $where[] = ['is_seel', '<>',1];
        $list = Db::name('order_skuinfo')
            ->field('
                sum(realcount) as skuquantity,
                new_sku as sku
            ')
            ->where($where)
            ->group('new_sku')
            ->order($order)
            ->paginate($limit);

        $lists = $list->items();
            
        foreach ($lists as &$item) {  
            $item['skuquantity'] = intval($item['skuquantity']);
            $refunds_info = Db::name('customer_refunds_info')
                ->field('SUM(num) as num')
                ->where('skus',$item['sku'])
                ->where($where2)
                ->select()->toarray();
                
            $item['OrderFeedbackTotal'] = $refunds_info[0]['num'] ?? 0;
            if($item['OrderFeedbackTotal'] > 0){
                $item['proportion'] = (float)number_format((float)$item['OrderFeedbackTotal'] / (float)$item['skuquantity']*100, 3, '.', '')."%";
            }else{
                $item['proportion'] = "0%";
            }
        }
        $data = [
            'list' => $lists,
            'total' => $list->total(),
        ]; 
        return json(DefaultResponse(200, true, '获取成功', $data));
    }


    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 店铺反馈列表
     * @author zhangheng
     */
    public function StoreFeedbackList($params = [])
    {
        $limit = $params['limit'] ?? 10;
        $createtime = $params['createtime'] ?? "";
        $spu = $params['spu'] ?? "";

        $where = [];

        if (!empty($createtime)) {
            $where[] = ['createtime', '>=', strtotime($createtime['0'])];
            $where[] = ['createtime', '<=', strtotime($createtime['1']) + 3600*24-1];

            $where2[] = ['createtime', '>=', $createtime['0']];
            $where2[] = ['createtime', '<=', date('Y-m-d H:i:s', strtotime($createtime['1']) + 3600*24-1)];
        }else{
            return json(DefaultResponse(201, true, '请填写下单时间'));
        }
        if (!empty($spu)) {
            $where[] = ['spu', 'LIKE', '%' . trim($spu) . '%'];
        }
        $order['skuquantity'] = 'desc';
        $where[]  = ['financial_status', 'in', ["partially_paid", "paid", "partially_refunded", "refunded"]];
        $where[] = ['is_seel', '<>',1];
        $list = Db::name('order_skuinfo')
            ->field('
                sum(realcount) as skuquantity,
                spu as spu
            ')
            ->where($where)
            ->group('spu')
            ->order($order)
            ->paginate($limit);

        $lists = $list->items();
            
        foreach ($lists as &$item) {  
            $item['skuquantity'] = intval($item['skuquantity']);
            $refunds_info = Db::name('customer_refunds_info')
                ->field('SUM(num) as num')
                ->where('spu',$item['spu'])
                ->where($where2)
                ->select()->toarray();
                
            $item['OrderFeedbackTotal'] = $refunds_info[0]['num'] ?? 0;
            if($item['OrderFeedbackTotal'] > 0){
                $item['proportion'] = (float)number_format((float)$item['OrderFeedbackTotal'] / (float)$item['skuquantity']*100, 3, '.', '')."%";
            }else{
                $item['proportion'] = "0%";
            }
        }
        $data = [
            'list' => $lists,
            'total' => $list->total(),
        ]; 
        return json(DefaultResponse(200, true, '获取成功', $data));
    }

    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 售后反馈列表
     * @author zhangheng
     */
    public function AfterSalesFeedbackList($params = [])
    {
        $limit = $params['limit'] ?? 10;
        $createtime = $params['createtime'] ?? "";
        $create_time = $params['create_time'] ?? "";
        $skus = $params['skus'] ?? "";
        $shop_id = $params['shop_id'] ?? "";
        $ReasonForRefund = $params['ReasonForRefund'] ?? [];

        $where = [];
        
        // ReasonForRefundId
        $ReasonForRefundId = $params['ReasonForRefundId'] ?? 0;
        if($ReasonForRefundId == 0){
            return json(DefaultResponse(201, true, '请选择售后原因'));
        }else{
            $where[] = ['ReasonForRefund', '=', $ReasonForRefundId];
        }

        if (!empty($createtime)) {
            $where[] = ['createtime', '>=', $createtime['0']];
            $where[] = ['createtime', '<=', date('Y-m-d H:i:s', strtotime($createtime['1']) + 3600*24-1)];
        }
        if (!empty($create_time)) {
            $where[] = ['create_time', '>=', $create_time['0']];
            $where[] = ['create_time', '<=', date('Y-m-d H:i:s', strtotime($create_time['1']) + 3600*24-1)];
        }
        if (!empty($shop_id)) {
            $where[] = ['shop_id', '=', $shop_id];
        }
        if (!empty($skus)) {
            $where[] = ['skus', 'like', '%' . trim($skus) . '%'];
        }
        
        if(!empty($ReasonForRefund)){
            $where[] = ['ReasonForRefund', 'in', $ReasonForRefund];
        }
        
        $list = Db::name('customer_refunds_info')
            ->field('
                SUM(num) as quantity,
                spu
            ')
            ->where($where)
            ->group('spu')
            ->order('quantity desc')
            ->paginate($limit);

        $lists = $list->items();
        
        $quantitys = Db::name('customer_refunds_info')
            ->field('
                SUM(num) as quantity
            ')
            ->where($where)
            ->select()->toarray();
        $quantitys = $quantitys[0]['quantity'] ?? 0;
        // halt($quantitys);
        
        foreach ($lists as &$item) {
            if ($item['quantity'] > 0 && $item['quantity'] > 0) {
                $item['percentage'] = number_format($item['quantity'] / $quantitys * 100, 2) . "%";
            }else{
                $item['percentage'] = 0;
            }
        }

        $data = [
            'list' => $lists,
            'total' => $list->total(),
        ];
        return json(DefaultResponse(200, true, '获取成功', $data));
    }

    
    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 售后反馈列表
     * @author zhangheng
     */
    public function AfterSalessalesinfo($params = [])
    {
        $limit = $params['limit'] ?? 10;
        $createtime = $params['createtime'] ?? "";
        $create_time = $params['create_time'] ?? "";
        $skus = $params['skus'] ?? "";
        $shop_id = $params['shop_id'] ?? "";
        $ReasonForRefund = $params['ReasonForRefund'] ?? [];

        $where = [];
        // spu
        $spu = $params['spu'] ?? "";
        if(empty($spu)){
            return json(DefaultResponse(201, true, '请选择SPU'));
        }else{
            $where[] = ['spu', '=', $spu];
        }

        if (!empty($createtime)) {
            $where[] = ['createtime', '>=', $createtime['0']];
            $where[] = ['createtime', '<=', date('Y-m-d H:i:s', strtotime($createtime['1']) + 3600*24-1)];
        }
        if (!empty($create_time)) {
            $where[] = ['create_time', '>=', $create_time['0']];
            $where[] = ['create_time', '<=', date('Y-m-d H:i:s', strtotime($create_time['1']) + 3600*24-1)];
        }
        if (!empty($shop_id)) {
            $where[] = ['shop_id', '=', $shop_id];
        }
        if (!empty($skus)) {
            $where[] = ['skus', 'like', '%' . trim($skus) . '%'];
        }
        
        if(!empty($ReasonForRefund)){
            $where[] = ['ReasonForRefund', 'in', $ReasonForRefund];
        }

        $RefundList = Db::name('order_refundreason')->where('status',0)->order('sort asc')->select()->toarray();
        $RefundList = array_column($RefundList, 'title', 'id');
        
        $list = Db::name('customer_refunds_info')
            ->field('
                SUM(num) as quantity,
                ReasonForRefund
            ')
            ->where($where)
            ->group('ReasonForRefund')
            ->order('quantity desc')
            ->paginate($limit);

        $lists = $list->items();
        
        $quantitys = Db::name('customer_refunds_info')
            ->field('
                SUM(num) as quantity
            ')
            ->where($where)
            ->select()->toarray();
        $quantitys = $quantitys[0]['quantity'] ?? 0;
        
        foreach ($lists as &$item) {
            $item['ReasonForRefundId'] = $item['ReasonForRefund'] ?? '';
            $item['ReasonForRefund'] = $RefundList[$item['ReasonForRefund']] ?? '';
            if ($item['quantity'] > 0 && $item['quantity'] > 0) {
                $item['percentage'] = number_format($item['quantity'] / $quantitys * 100, 2) . "%";
            }else{
                $item['percentage'] = 0;
            }
        }

        $data = [
            'list' => $lists,
            'total' => $list->total(),
        ];
        return json(DefaultResponse(200, true, '获取成功', $data));
    }

    
    
    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 售后反馈列表
     * @author zhangheng
     */
    public function AfterSalessalesinfob($params = [])
    {
        $limit = $params['limit'] ?? 10;
        $createtime = $params['createtime'] ?? "";
        $create_time = $params['create_time'] ?? "";
        $skus = $params['skus'] ?? "";
        $shop_id = $params['shop_id'] ?? "";
        $ReasonForRefund = $params['ReasonForRefund'] ?? [];

        $where = [];
        // spu
        $sku = $params['sku'] ?? "";
        if(empty($sku)){
            return json(DefaultResponse(201, true, '请选择SKU'));
        }else{
            $where[] = ['skus', '=', $sku];
        }

        if (!empty($createtime)) {
            $where[] = ['createtime', '>=', $createtime['0']];
            $where[] = ['createtime', '<=', date('Y-m-d H:i:s', strtotime($createtime['1']) + 3600*24-1)];
        }
        if (!empty($create_time)) {
            $where[] = ['create_time', '>=', $create_time['0']];
            $where[] = ['create_time', '<=', date('Y-m-d H:i:s', strtotime($create_time['1']) + 3600*24-1)];
        }
        if (!empty($shop_id)) {
            $where[] = ['shop_id', '=', $shop_id];
        }
        
        if(!empty($ReasonForRefund)){
            $where[] = ['ReasonForRefund', 'in', $ReasonForRefund];
        }

        $RefundList = Db::name('order_refundreason')->where('status',0)->order('sort asc')->select()->toarray();
        $RefundList = array_column($RefundList, 'title', 'id');
        
        $list = Db::name('customer_refunds_info')
            ->field('
                SUM(num) as quantity,
                ReasonForRefund
            ')
            ->where($where)
            ->group('ReasonForRefund')
            ->order('quantity desc')
            ->paginate($limit);

        $lists = $list->items();
        
        $quantitys = Db::name('customer_refunds_info')
            ->field('
                SUM(num) as quantity
            ')
            ->where($where)
            ->select()->toarray();
        $quantitys = $quantitys[0]['quantity'] ?? 0;
        
        foreach ($lists as &$item) {
            $item['ReasonForRefundId'] = $item['ReasonForRefund'] ?? '';
            $item['ReasonForRefund'] = $RefundList[$item['ReasonForRefund']] ?? '';
            if ($item['quantity'] > 0 && $item['quantity'] > 0) {
                $item['percentage'] = number_format($item['quantity'] / $quantitys * 100, 2) . "%";
            }else{
                $item['percentage'] = 0;
            }
        }

        $data = [
            'list' => $lists,
            'total' => $list->total(),
        ];
        return json(DefaultResponse(200, true, '获取成功', $data));
    }
    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 退款列表
     * @author zhangheng
     */
    public function RefundFeedbackList($params = [])
    {
        $limit = $params['limit'] ?? 10;
        $createtime = $params['createtime'] ?? "";
        $created_at = $params['created_at'] ?? "";
        $spu = $params['spu'] ?? "";

        $where = [];

        //created_at 售后时间
        if (!empty($created_at)) {
            $where[] = ['a.created_at', '>=', strtotime($created_at['0'])];
            $where[] = ['a.created_at', '<=', strtotime($created_at['1']) + 3600*24-1];
        }
        //created_at 订单时间
        if (!empty($createtime)) {
            $where[] = ['a.createtime', '>=', strtotime($createtime['0'])];
            $where[] = ['a.createtime', '<=', strtotime($createtime['1']) + 3600*24-1];
        }else{
            // $yesterday = strtotime(date('Y-m-d', strtotime('-30 day') - timezone())); 
            // $where[] = ['a.createtime', '>=', $yesterday];
            // $where[] = ['a.createtime', '<=', $yesterday + 3600*24-1];
        }
        if (empty($spu)) {
            return json(DefaultResponse(201, true, '请填写spu搜索'));
        }

        //时间搜素
        $date = $params['date'] ?? "";
        $customDate = $params['customDate'] ?? 0;
        if(!empty($customDate)){
            switch ($customDate) {
                case '1':
                    //实时
                    $yesterday = date('Y-m-d', time() - timezone());  
                    $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                    $endOfYesterday  = strtotime($yesterday . ' 23:59:59');
                    $where[]  = ['a.createtime', '>=', $midnightYesterday];
                    $where[]  = ['a.createtime', '<', $endOfYesterday]; 

                    break;
                case '2':
                    $yesterday = date('Y-m-d', strtotime('-1 day') - timezone());
                    $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                    $endOfYesterday  = strtotime($yesterday . ' 23:59:59');
                    $where[]  = ['a.createtime', '>=', $midnightYesterday];
                    $where[]  = ['a.createtime', '<', $endOfYesterday]; 

                    break;
                case '3':
                    $yesterday = strtotime(date('Y-m-d', time() - timezone()));  
                    $yesterdayEnd = strtotime(date('Y-m-d', strtotime('-6 day') - timezone()));  
                    $where[] = ['a.createtime', '<', $yesterday];
                    $where[] = ['a.createtime', '>=', $yesterdayEnd];

                    break;
                default:
                    $yesterday = date('Y-m-d', strtotime('-1 day') - timezone());
                    $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                    $endOfYesterday  = strtotime($yesterday . ' 23:59:59');
                    $where[]  = ['a.createtime', '>=', $midnightYesterday];
                    $where[]  = ['a.createtime', '<', $endOfYesterday]; 
            }
        }else{
            // 当自定义时间为空时 查日期有没有值
            if (!empty($date)) {
                $where[] = ['a.createtime', '>=', strtotime($date['0'])];
                $where[] = ['a.createtime', '<=', strtotime($date['1']) + 3600*24-1];
            }else{
                // $yesterday = date('Y-m-d', strtotime('-1 day') - timezone());
                // $midnightYesterday = strtotime($yesterday . ' 00:00:00');
                // $endOfYesterday  = strtotime($yesterday . ' 23:59:59');
                // $where[]  = ['a.createtime', '>=', $midnightYesterday];
                // $where[]  = ['a.createtime', '<=', $endOfYesterday]; 
            }
        }

        $list = Db::name('order_skuinfo')  
            ->field('
                c.realcount,
                d.name as shop_name,
                a.order_id,a.name,a.financial_status,a.disputeapi_status
            ')
            ->alias('c')
            ->join('qu_order_achievement a','c.order_id = a.order_id', 'left')
            ->join('qu_shops d','d.id = a.shop_id', 'left')
            ->where(function ($query) use ($where,$spu) {  
                $query->where($where)  
                    ->where('c.spu',$spu)
                    ->where('a.financial_status',"in",["partially_refunded", "refunded", "partially_paid", "pending", "voided"]);
            })  
            ->whereOr(function ($query) use ($where,$spu) {  
                $query->where($where)  
                    ->where('c.spu',$spu)
                    ->where('a.disputeapi_status',"<>","暂无争议");
            })
            // ->select()->toArray();
            ->paginate($limit);

            $lists = $list->items();
        
        $data = [
            'list' => $lists,
            'total' => $list->total(),
        ];
        return json(DefaultResponse(200, true, '获取成功', $data));
    }

    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 订单反馈详情
     * @author zhangheng
     */
    public function OrderFeedbackList($params = [])
    {
        $limit = $params['limit'] ?? 10;
        $createtime = $params['createtime'] ?? "";
        $create_time = $params['create_time'] ?? "";
        $skus = $params['skus'] ?? "";
        $shop_id = $params['shop_id'] ?? "";
        $ReasonForRefund = $params['ReasonForRefund'] ?? [];

        $where = [];

        if (!empty($createtime)) {
            $where[] = ['createtime', '>=', $createtime['0']];
            $where[] = ['createtime', '<=', date('Y-m-d H:i:s', strtotime($createtime['1']) + 3600*24-1)];
        }
        if (!empty($create_time)) {
            $where[] = ['create_time', '>=', $create_time['0']];
            $where[] = ['create_time', '<=', date('Y-m-d H:i:s', strtotime($create_time['1']) + 3600*24-1)];
        }
        if (!empty($shop_id)) {
            $where[] = ['shop_id', '=', $shop_id];
        }
        if (!empty($skus)) {
            $where[] = ['skus', 'like', '%' . trim($skus) . '%'];
        }
        
        if(!empty($ReasonForRefund)){
            $where[] = ['ReasonForRefund', 'in', $ReasonForRefund];
        }

        $RefundList = Db::name('order_refundreason')->where('status',0)->order('sort asc')->select()->toarray();
        $RefundList = array_column($RefundList, 'title', 'id');
        
        $list = Db::name('customer_refunds_info')
            ->field('
                SUM(num) as quantity,
                ReasonForRefund
            ')
            ->where($where)
            ->group('ReasonForRefund')
            ->order('quantity desc')
            ->paginate($limit);

        $lists = $list->items();
        
        $quantitys = Db::name('customer_refunds_info')
            ->field('
                SUM(num) as quantity
            ')
            ->where($where)
            ->select()->toarray();
        $quantitys = $quantitys[0]['quantity'] ?? 0;
        
        foreach ($lists as &$item) {
            $item['ReasonForRefundId'] = $item['ReasonForRefund'] ?? '';
            $item['ReasonForRefund'] = $RefundList[$item['ReasonForRefund']] ?? '';
            if ($item['quantity'] > 0 && $item['quantity'] > 0) {
                $item['percentage'] = number_format($item['quantity'] / $quantitys * 100, 2) . "%";
            }else{
                $item['percentage'] = 0;
            }
        }

        $data = [
            'list' => $lists,
            'total' => $list->total(),
        ];
        return json(DefaultResponse(200, true, '获取成功', $data));

    }


    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 导出
     * @author zhangheng
     */
    public function export($params = [])
    {
        $lists = [];

        $active = $params['active'] ?? "";
        switch ($active) {
            case 'ProductTable':
                $lists = $this->ProductTable($params);
                $header = [];
                $header[] = ['SKU', 'sku'];
                $header[] = ['销售件数', 'skuquantity'];
                $header[] = ['售后件数', 'OrderFeedbackTotal'];
                $header[] = ['售后率', 'proportion'];
                break;
            case 'OrderTable':
                $lists = $this->OrderTable($params);
                $header = [];
                $header[] = ['售后类型', 'ReasonForRefund'];
                $header[] = ['数量', 'quantity'];
                $header[] = ['售后率', 'percentage'];
                break;
            case 'ShopTable':
                $lists = $this->ShopTable($params);
                $header = [];
                $header[] = ['SPU', 'spu'];
                $header[] = ['销售件数', 'skuquantity'];
                $header[] = ['售后件数', 'OrderFeedbackTotal'];
                $header[] = ['售后率', 'proportion'];
                break;
            default:
                $lists = [];
        }
        $fileName = "OrderFeedback" . time();
        return Excel::exportData($lists, $header, $fileName, 'xlsx');
    }

    /**
    * @author  zhangheng
    */
    public function ProductTable($params)
    {
        $createtime = $params['createtime'] ?? "";
        $skus = $params['skus'] ?? "";

        $where = [];

        if (!empty($createtime)) {
            $where[] = ['createtime', '>=', strtotime($createtime['0'])];
            $where[] = ['createtime', '<=', strtotime($createtime['1']) + 3600*24-1];

            $where2[] = ['createtime', '>=', $createtime['0']];
            $where2[] = ['createtime', '<=', date('Y-m-d H:i:s', strtotime($createtime['1']) + 3600*24-1)];
        }else{
            return [];
        }
        if (!empty($skus)) {
            $where[] = ['new_sku', 'LIKE', '%' . trim($skus) . '%'];
        }
        $order['skuquantity'] = 'desc';
        $where[]  = ['financial_status', 'in', ["partially_paid", "paid", "partially_refunded", "refunded"]];
        $where[] = ['is_seel', '<>',1];
        $lists = Db::name('order_skuinfo')
            ->field('
                sum(realcount) as skuquantity,
                new_sku as sku
            ')
            ->where($where)
            ->group('new_sku')
            ->order($order)
            ->limit(200)
            ->select()->toarray();
        
        foreach ($lists as &$item) {  
            $item['skuquantity'] = intval($item['skuquantity']);
            $refunds_info = Db::name('customer_refunds_info')
                ->field('SUM(num) as num')
                ->where('skus',$item['sku'])
                ->where($where2)
                ->select()->toarray();
                
            $item['OrderFeedbackTotal'] = $refunds_info[0]['num'] ?? 0;
            if($item['OrderFeedbackTotal'] > 0){
                $item['proportion'] = (float)number_format((float)$item['OrderFeedbackTotal'] / (float)$item['skuquantity']*100, 3, '.', '')."%";
            }else{
                $item['proportion'] = "0%";
            }
        }
        return $lists;
    }
    
    /**
    * @author  zhangheng
    */
    public function OrderTable($params)
    {
        $createtime = $params['createtime'] ?? "";
        $create_time = $params['create_time'] ?? "";
        $skus = $params['skus'] ?? "";
        $shop_id = $params['shop_id'] ?? "";
        $ReasonForRefund = $params['ReasonForRefund'] ?? [];

        $where = [];

        if (!empty($createtime)) {
            $where[] = ['createtime', '>=', $createtime['0']];
            $where[] = ['createtime', '<=', date('Y-m-d H:i:s', strtotime($createtime['1']) + 3600*24-1)];
        }
        if (!empty($create_time)) {
            $where[] = ['create_time', '>=', $create_time['0']];
            $where[] = ['create_time', '<=', date('Y-m-d H:i:s', strtotime($create_time['1']) + 3600*24-1)];
        }
        if (!empty($shop_id)) {
            $where[] = ['shop_id', '=', $shop_id];
        }
        if (!empty($skus)) {
            $where[] = ['skus', 'like', '%' . trim($skus) . '%'];
        }
        
        if(!empty($ReasonForRefund)){
            $where[] = ['ReasonForRefund', 'in', $ReasonForRefund];
        }

        $RefundList = Db::name('order_refundreason')->where('status',0)->order('sort asc')->select()->toarray();
        $RefundList = array_column($RefundList, 'title', 'id');
        
        $lists = Db::name('customer_refunds_info')
            ->field('
                SUM(num) as quantity,
                ReasonForRefund
            ')
            ->where($where)
            ->group('ReasonForRefund')
            ->order('quantity desc')
            ->select()->toarray();
        
        $quantitys = Db::name('customer_refunds_info')
            ->field('
                SUM(num) as quantity
            ')
            ->where($where)
            ->select()->toarray();
        $quantitys = $quantitys[0]['quantity'] ?? 0;
        
        foreach ($lists as &$item) {
            $item['ReasonForRefundId'] = $item['ReasonForRefund'] ?? '';
            $item['ReasonForRefund'] = $RefundList[$item['ReasonForRefund']] ?? '';
            if ($item['quantity'] > 0 && $item['quantity'] > 0) {
                $item['percentage'] = number_format($item['quantity'] / $quantitys * 100, 2) . "%";
            }else{
                $item['percentage'] = 0;
            }
        }
        return $lists;
    }

    /**
    * @author  zhangheng
    */
    public function ShopTable($params)
    {
        $createtime = $params['createtime'] ?? "";
        $spu = $params['spu'] ?? "";

        $where = [];

        if (!empty($createtime)) {
            $where[] = ['createtime', '>=', strtotime($createtime['0'])];
            $where[] = ['createtime', '<=', strtotime($createtime['1']) + 3600*24-1];

            $where2[] = ['createtime', '>=', $createtime['0']];
            $where2[] = ['createtime', '<=', date('Y-m-d H:i:s', strtotime($createtime['1']) + 3600*24-1)];
        }else{
            return [];
        }
        if (!empty($spu)) {
            $where[] = ['spu', 'LIKE', '%' . trim($spu) . '%'];
        }
        $order['skuquantity'] = 'desc';
        $where[]  = ['financial_status', 'in', ["partially_paid", "paid", "partially_refunded", "refunded"]];
        $where[] = ['is_seel', '<>',1];
        $lists = Db::name('order_skuinfo')
            ->field('
                sum(realcount) as skuquantity,
                spu as spu
            ')
            ->where($where)
            ->group('spu')
            ->limit(200)
            ->order($order)
            ->select()->toarray();
            
        foreach ($lists as &$item) {  
            $item['skuquantity'] = intval($item['skuquantity']);
            $refunds_info = Db::name('customer_refunds_info')
                ->field('SUM(num) as num')
                ->where('spu',$item['spu'])
                ->where($where2)
                ->select()->toarray();
                
            $item['OrderFeedbackTotal'] = $refunds_info[0]['num'] ?? 0;
            if($item['OrderFeedbackTotal'] > 0){
                $item['proportion'] = (float)number_format((float)$item['OrderFeedbackTotal'] / (float)$item['skuquantity']*100, 3, '.', '')."%";
            }else{
                $item['proportion'] = "0%";
            }
        }
        return $lists;
    }
    
    
}
